package com.wifi.channel.dao;

import com.sun.org.apache.xerces.internal.impl.dv.DVFactoryException;
import com.wifi.channel.entity.Channel;
import com.wifi.channel.entity.ChannelClass;
import com.wifi.unti.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ChannelDaoImpl implements  ChannelDao{
    @Override
    public List<Object> queryChannel(Integer start, Integer pageSize, String class1id, String class2id, String title) throws SQLException {
        int count=0;
        StringBuffer stringBuffer=new StringBuffer(" SELECT m.*, n.name class1Name ,l.name class2Name from tb_channel m left join tb_channel_class n on m.class1=n.id and n.status!=1 left JOIN tb_channel_class l on m.class2=l.id and l.status!=1  ");
//        String sql = "SELECT COUNT(*)  FROM `shenzhou`.`tb_channel` ";
        if (!class1id.isEmpty()){ stringBuffer.append(" where m.class1= ");
            stringBuffer.append(Integer.valueOf(class1id));
            count++;}
        if (!class2id.isEmpty()){ if (count>0){
            stringBuffer.append(" and m.class2= ");
            stringBuffer.append(Integer.valueOf(class2id));
            count++;
        }else{
            stringBuffer.append(" where m.class2= ");
            stringBuffer.append(Integer.valueOf(class2id));
            count++;
        }}
        if (!title.isEmpty()){
            StringBuffer titlei=new StringBuffer(" '%");
                    titlei.append(title);
            titlei.append("%' ");
            if (count>0){
            stringBuffer.append(" and (m.title like ");
            stringBuffer.append(titlei);
                stringBuffer.append(" or m.name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(" or m.onshow_name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(")");

            count++;
        }else{
            stringBuffer.append(" where( m.title like ");
            stringBuffer.append(titlei);
                stringBuffer.append(" or m.name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(" or m.onshow_name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(")");
            count++; }
        }stringBuffer.append(" limit ?,? ");
        String sql=stringBuffer.toString();
        System.out.println(sql);
        return DBHelper.selectAll(sql,new Channel(),start,pageSize);
    }

    @Override
    public long selectCount(String class1id, String class2id, String title) throws SQLException {
        int count=0;
        QueryRunner runner= new QueryRunner(DBHelper.getDataSource());
        StringBuffer stringBuffer=new StringBuffer("SELECT COUNT(*)  FROM `shenzhou`.`tb_channel` ");
//        String sql = "SELECT COUNT(*)  FROM `shenzhou`.`tb_channel` ";
        if (!class1id.isEmpty()){ stringBuffer.append(" where class1= ");
        stringBuffer.append(Integer.valueOf(class1id));
        count++;}
        if (!class2id.isEmpty()){ if (count>0){
            stringBuffer.append(" and class2= ");
            stringBuffer.append(Integer.valueOf(class2id));
            count++;
        }else{
            stringBuffer.append(" where class2= ");
            stringBuffer.append(Integer.valueOf(class2id));
            count++;
        }}
        if (!title.isEmpty()){
            StringBuffer titlei=new StringBuffer(" '%");
            titlei.append(title);
            titlei.append("%'");
            if (count>0){
            stringBuffer.append(" and title like ");
            stringBuffer.append(titlei);
                stringBuffer.append(" or name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(" or onshow_name like ");
                stringBuffer.append(titlei);
            count++;
        }else{
            stringBuffer.append(" where title like ");
            stringBuffer.append(titlei);
                stringBuffer.append(" or name like ");
                stringBuffer.append(titlei);
                stringBuffer.append(" or onshow_name like ");
                stringBuffer.append(titlei);
            count++; }
    }
        String sql=stringBuffer.toString();
        System.out.println(sql);
        long i = runner.query(sql,new ScalarHandler<Long>());
        return i;}

    @Override
    public List<Object> queryChannelClass1() throws SQLException {
        String sql="SELECT * FROM `shenzhou`.`tb_channel_class` where pid=0";
        return DBHelper.selectAll(sql,new ChannelClass());
    }

    @Override
    public List<Object> queryChannelClass2(String pid) throws SQLException {
        StringBuffer stringBuffer=new StringBuffer("SELECT * FROM `shenzhou`.`tb_channel_class` ");
        if (!pid.isEmpty()){
            stringBuffer.append( " where pid=");
            stringBuffer.append(Integer.valueOf(pid));
        }else{stringBuffer.append("where pid!=0");}
        String sql=stringBuffer.toString();
        System.out.println(sql);
        return DBHelper.selectAll(sql,new ChannelClass());
    }

    @Override
    public Object getCbyId(Integer id) throws SQLException {
        String sql="SELECT * FROM `shenzhou`.`tb_channel` where id =?";
        return DBHelper.select(sql,new Channel(),id);
    }

    @Override
    public int updateDel(Integer id, int status) throws SQLException {
        String sql="UPDATE `shenzhou`.`tb_channel` SET `is_del` = ? WHERE `id` = ?";
        return DBHelper.update(sql,status,id);
    }

    @Override
    public Integer updateUrl(String link, String id) throws SQLException {
        String sql="UPDATE `shenzhou`.`tb_channel` SET `url` = ? WHERE `id` = ?";
        return DBHelper.update(sql,link,Integer.valueOf(id));
    }

    @Override
    public Object confirmChannel(String name, Integer class1, Integer class2) throws SQLException {
        String sql="SELECT * FROM `shenzhou`.`tb_channel` where name= ? and class1=? and class2=?";
        return DBHelper.select(sql,new Channel(),name,class1,class2);
    }

    @Override
    public Long insert(Channel channel) throws SQLException {
        String sql="INSERT INTO `shenzhou`.`tb_channel`(`name`, `class1`, `class2`, `url`, `onshow_name`, `title`, `create_time`) VALUES (?, ?, ?, ?, ?,?, now())";
        return DBHelper.insert(sql,channel.getName(),channel.getClass1(),channel.getClass2(),channel.getUrl(),channel.getOnshowName(),channel.getTitle());
    }
}
